const { DataTypes } = require('sequelize');

/**
 * 创建基础表结构的迁移文件
 */
module.exports = {
  up: async (queryInterface, Sequelize) => {
    // 创建用户表
    await queryInterface.createTable('users', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      username: {
        type: DataTypes.STRING(50),
        allowNull: false,
        unique: true
      },
      password: {
        type: DataTypes.STRING(255),
        allowNull: false
      },
      nickname: {
        type: DataTypes.STRING(100),
        allowNull: true
      },
      email: {
        type: DataTypes.STRING(100),
        allowNull: true,
        unique: true
      },
      phone: {
        type: DataTypes.STRING(20),
        allowNull: true
      },
      avatar: {
        type: DataTypes.TEXT,
        allowNull: true
      },
      gender: {
        type: DataTypes.ENUM('male', 'female', 'other'),
        allowNull: true
      },
      birth_date: {
        type: DataTypes.DATEONLY,
        allowNull: true
      },
      role: {
        type: DataTypes.ENUM('admin', 'teacher', 'user'),
        defaultValue: 'user'
      },
      status: {
        type: DataTypes.ENUM('active', 'inactive', 'banned'),
        defaultValue: 'active'
      },
      level: {
        type: DataTypes.INTEGER,
        defaultValue: 1
      },
      experience: {
        type: DataTypes.INTEGER,
        defaultValue: 0
      },
      total_study_time: {
        type: DataTypes.INTEGER,
        defaultValue: 0,
        comment: '总学习时长(秒)'
      },
      practice_count: {
        type: DataTypes.INTEGER,
        defaultValue: 0,
        comment: '练习次数'
      },
      points: {
        type: DataTypes.INTEGER,
        defaultValue: 0,
        comment: '积分'
      },
      achievements: {
        type: DataTypes.JSON,
        allowNull: true,
        comment: '成就列表'
      },
      last_login_at: {
        type: DataTypes.DATE,
        allowNull: true
      },
      created_at: {
        type: DataTypes.DATE,
        allowNull: false,
        defaultValue: Sequelize.NOW
      },
      updated_at: {
        type: DataTypes.DATE,
        allowNull: false,
        defaultValue: Sequelize.NOW
      }
    }, {
      charset: 'utf8mb4',
      collate: 'utf8mb4_unicode_ci'
    });

    // 创建课程分类表
    await queryInterface.createTable('course_categories', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      name: {
        type: DataTypes.STRING(100),
        allowNull: false
      },
      description: {
        type: DataTypes.TEXT,
        allowNull: true
      },
      parent_id: {
        type: DataTypes.INTEGER,
        allowNull: true,
        references: {
          model: 'course_categories',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL'
      },
      sort_order: {
        type: DataTypes.INTEGER,
        defaultValue: 0
      },
      is_active: {
        type: DataTypes.BOOLEAN,
        defaultValue: true
      },
      created_at: {
        type: DataTypes.DATE,
        allowNull: false,
        defaultValue: Sequelize.NOW
      },
      updated_at: {
        type: DataTypes.DATE,
        allowNull: false,
        defaultValue: Sequelize.NOW
      }
    }, {
      charset: 'utf8mb4',
      collate: 'utf8mb4_unicode_ci'
    });

    // 创建课程表
    await queryInterface.createTable('courses', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      title: {
        type: DataTypes.STRING(200),
        allowNull: false
      },
      description: {
        type: DataTypes.TEXT,
        allowNull: true
      },
      category_id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        references: {
          model: 'course_categories',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT'
      },
      difficulty: {
        type: DataTypes.ENUM('beginner', 'intermediate', 'advanced'),
        defaultValue: 'beginner'
      },
      is_free: {
        type: DataTypes.BOOLEAN,
        defaultValue: true
      },
      price: {
        type: DataTypes.DECIMAL(10, 2),
        defaultValue: 0
      },
      cover_image: {
        type: DataTypes.TEXT,
        allowNull: true
      },
      video_url: {
        type: DataTypes.TEXT,
        allowNull: true
      },
      audio_url: {
        type: DataTypes.TEXT,
        allowNull: true
      },
      content: {
        type: DataTypes.TEXT,
        allowNull: true
      },
      duration: {
        type: DataTypes.INTEGER,
        defaultValue: 0,
        comment: '课程时长(秒)'
      },
      views: {
        type: DataTypes.INTEGER,
        defaultValue: 0
      },
      likes: {
        type: DataTypes.INTEGER,
        defaultValue: 0
      },
      enrollments: {
        type: DataTypes.INTEGER,
        defaultValue: 0
      },
      is_published: {
        type: DataTypes.BOOLEAN,
        defaultValue: false
      },
      published_at: {
        type: DataTypes.DATE,
        allowNull: true
      },
      created_at: {
        type: DataTypes.DATE,
        allowNull: false,
        defaultValue: Sequelize.NOW
      },
      updated_at: {
        type: DataTypes.DATE,
        allowNull: false,
        defaultValue: Sequelize.NOW
      }
    }, {
      charset: 'utf8mb4',
      collate: 'utf8mb4_unicode_ci'
    });

    // 创建评测记录表
    await queryInterface.createTable('evaluation_records', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      user_id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        references: {
          model: 'users',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      text_content: {
        type: DataTypes.TEXT,
        allowNull: false
      },
      audio_file: {
        type: DataTypes.STRING(255),
        allowNull: true
      },
      score: {
        type: DataTypes.DECIMAL(5, 2),
        allowNull: false
      },
      accuracy: {
        type: DataTypes.DECIMAL(5, 2),
        allowNull: true
      },
      fluency: {
        type: DataTypes.DECIMAL(5, 2),
        allowNull: true
      },
      completeness: {
        type: DataTypes.DECIMAL(5, 2),
        allowNull: true
      },
      suggestions: {
        type: DataTypes.JSON,
        allowNull: true
      },
      detail_scores: {
        type: DataTypes.JSON,
        allowNull: true
      },
      duration: {
        type: DataTypes.INTEGER,
        defaultValue: 0,
        comment: '录音时长(秒)'
      },
      created_at: {
        type: DataTypes.DATE,
        allowNull: false,
        defaultValue: Sequelize.NOW
      },
      updated_at: {
        type: DataTypes.DATE,
        allowNull: false,
        defaultValue: Sequelize.NOW
      }
    }, {
      charset: 'utf8mb4',
      collate: 'utf8mb4_unicode_ci'
    });

    // 创建游戏记录表
    await queryInterface.createTable('game_records', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      user_id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        references: {
          model: 'users',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      game_type: {
        type: DataTypes.STRING(50),
        allowNull: false
      },
      target_word: {
        type: DataTypes.STRING(100),
        allowNull: false
      },
      user_pronunciation: {
        type: DataTypes.STRING(255),
        allowNull: true
      },
      score: {
        type: DataTypes.DECIMAL(5, 2),
        allowNull: false
      },
      similarity: {
        type: DataTypes.DECIMAL(5, 2),
        allowNull: true
      },
      audio_file: {
        type: DataTypes.STRING(255),
        allowNull: true
      },
      duration: {
        type: DataTypes.INTEGER,
        defaultValue: 0
      },
      created_at: {
        type: DataTypes.DATE,
        allowNull: false,
        defaultValue: Sequelize.NOW
      },
      updated_at: {
        type: DataTypes.DATE,
        allowNull: false,
        defaultValue: Sequelize.NOW
      }
    }, {
      charset: 'utf8mb4',
      collate: 'utf8mb4_unicode_ci'
    });

    // 创建每日记录表
    await queryInterface.createTable('daily_records', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      user_id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        references: {
          model: 'users',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      date: {
        type: DataTypes.DATEONLY,
        allowNull: false
      },
      content: {
        type: DataTypes.TEXT,
        allowNull: false
      },
      audio_file: {
        type: DataTypes.STRING(255),
        allowNull: true
      },
      video_file: {
        type: DataTypes.STRING(255),
        allowNull: true
      },
      score: {
        type: DataTypes.DECIMAL(5, 2),
        allowNull: true
      },
      duration: {
        type: DataTypes.INTEGER,
        defaultValue: 0
      },
      created_at: {
        type: DataTypes.DATE,
        allowNull: false,
        defaultValue: Sequelize.NOW
      },
      updated_at: {
        type: DataTypes.DATE,
        allowNull: false,
        defaultValue: Sequelize.NOW
      }
    }, {
      charset: 'utf8mb4',
      collate: 'utf8mb4_unicode_ci'
    });

    // 添加索引
    await queryInterface.addIndex('users', ['username']);
    await queryInterface.addIndex('users', ['email']);
    await queryInterface.addIndex('users', ['phone']);
    await queryInterface.addIndex('courses', ['category_id']);
    await queryInterface.addIndex('courses', ['difficulty']);
    await queryInterface.addIndex('courses', ['is_published']);
    await queryInterface.addIndex('evaluation_records', ['user_id']);
    await queryInterface.addIndex('game_records', ['user_id']);
    await queryInterface.addIndex('daily_records', ['user_id', 'date']);
  },

  down: async (queryInterface, Sequelize) => {
    // 删除表（按依赖关系逆序）
    await queryInterface.dropTable('daily_records');
    await queryInterface.dropTable('game_records');
    await queryInterface.dropTable('evaluation_records');
    await queryInterface.dropTable('courses');
    await queryInterface.dropTable('course_categories');
    await queryInterface.dropTable('users');
  }
};
